﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recRestructurRepo : ISearchable<recRestructur>
    {
        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recRestructur", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recRestructur", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recRestructur", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recRestructur", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recRestructur", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recRestructur", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recRestructur> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recRestructur> tt = new List<recRestructur>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      RestructurID, DebtorID, DebtorName, LetterNo, LetterDate, ActionRestructurID, ActionRestructurName, ScheduleTypeName, SchedulePayment, SumValues, RestructurValues, SumPayment, Balance, IsActive, AttachFile, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, AttachFileName ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recRestructur where IsActive = 1 ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recRestructur t = new recRestructur();
                                t.RestructurID = dr[recRestructur.Fields.RestructurID] == DBNull.Value ? 0 : int.Parse(dr[recRestructur.Fields.RestructurID].ToString());
                                t.DebtorID = dr[recRestructur.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recRestructur.Fields.DebtorID].ToString());
                                t.DebtorName = dr[recRestructur.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recRestructur.Fields.DebtorName].ToString();
                                t.LetterNo = dr[recRestructur.Fields.LetterNo] == DBNull.Value ? string.Empty : dr[recRestructur.Fields.LetterNo].ToString();
                                t.LetterDate = dr[recRestructur.Fields.LetterDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructur.Fields.LetterDate].ToString());
                                t.ActionRestructurID = dr[recRestructur.Fields.ActionRestructurID] == DBNull.Value ? string.Empty : dr[recRestructur.Fields.ActionRestructurID].ToString();
                                t.ActionRestructurName = dr[recRestructur.Fields.ActionRestructurName] == DBNull.Value ? string.Empty : dr[recRestructur.Fields.ActionRestructurName].ToString();
                                t.ScheduleTypeName = dr[recRestructur.Fields.ScheduleTypeName] == DBNull.Value ? string.Empty : dr[recRestructur.Fields.ScheduleTypeName].ToString();
                                t.SchedulePayment = dr[recRestructur.Fields.SchedulePayment] == DBNull.Value ? (int?)null : int.Parse(dr[recRestructur.Fields.SchedulePayment].ToString());
                                t.SumValues = dr[recRestructur.Fields.SumValues] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recRestructur.Fields.SumValues].ToString());
                                t.RestructurValues = dr[recRestructur.Fields.RestructurValues] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recRestructur.Fields.RestructurValues].ToString());
                                t.SumPayment = dr[recRestructur.Fields.SumPayment] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recRestructur.Fields.SumPayment].ToString());
                                t.Balance = dr[recRestructur.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recRestructur.Fields.Balance].ToString());
                                t.IsActive = dr[recRestructur.Fields.IsActive] == DBNull.Value ? (bool?)null : bool.Parse(dr[recRestructur.Fields.IsActive].ToString());
                                t.AttachFile = dr[recRestructur.Fields.AttachFile] == DBNull.Value ? null : (byte[])(dr[recRestructur.Fields.AttachFile]);
                                t.CreatedDate = dr[recRestructur.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructur.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recRestructur.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recRestructur.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recRestructur.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructur.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recRestructur.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recRestructur.Fields.UpdatedBy].ToString();
                                t.AttachFileName = dr[recRestructur.Fields.AttachFileName] == DBNull.Value ? string.Empty : dr[recRestructur.Fields.AttachFileName].ToString();
                                
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public recRestructur DetailFullObjectRestructure(int RestructureID)
        {
            recRestructur recRestructur = new recRestructur();
            recRestructur = this.GetById(RestructureID);

            recRestructur.recRestructurDetails = new List<recRestructurDetail>();
            #region Adding recRestructurDetail for each object recRestructur
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    string CommandSQl = "";
                    CommandSQl = @"SELECT  [RestructurDetailID]
                                              ,[RestructurID]
                                              ,[TransNo]
                                              ,[TransDemandNo]
                                              ,[SanctionNo]
                                              ,[TransTypeID]
                                              ,[TransTypeName]
                                              ,[TransValues]
                                              ,[CreatedDate]
                                              ,[CreatedBy]
                                              ,[UpdatedDate]
                                              ,[UpdatedBy]
                                          FROM [OJK Receivables].[transactions].[recRestructurDetail]
                                          where [RestructurID] = " + recRestructur.RestructurID;


                    cmd.CommandText = CommandSQl.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {

                                recRestructurDetail recdetal = new recRestructurDetail();
                                recdetal.RestructurDetailID = dr[recRestructurDetail.Fields.RestructurDetailID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurDetail.Fields.RestructurDetailID].ToString());
                                recdetal.RestructurID = dr[recRestructurDetail.Fields.RestructurID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurDetail.Fields.RestructurID].ToString());
                                recdetal.TransNo = dr[recRestructurDetail.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransNo].ToString();
                                recdetal.TransDemandNo = dr[recRestructurDetail.Fields.TransDemandNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransDemandNo].ToString();
                                recdetal.SanctionNo = dr[recRestructurDetail.Fields.SanctionNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.SanctionNo].ToString();
                                recdetal.TransTypeID = dr[recRestructurDetail.Fields.TransTypeID] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransTypeID].ToString();
                                recdetal.TransTypeName = dr[recRestructurDetail.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransTypeName].ToString();

                                recdetal.TransValues = dr[recRestructurDetail.Fields.TransValues] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recRestructurDetail.Fields.TransValues].ToString());

                                recdetal.CreatedDate = dr[recRestructurDetail.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurDetail.Fields.CreatedDate].ToString());
                                recdetal.CreatedBy = dr[recRestructurDetail.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.CreatedBy].ToString();
                                recdetal.UpdatedDate = dr[recRestructurDetail.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurDetail.Fields.UpdatedDate].ToString());
                                recdetal.UpdatedBy = dr[recRestructurDetail.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.UpdatedBy].ToString();

                                recRestructur.recRestructurDetails.Add(recdetal);
                                recdetal.Dispose();

                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }

            #endregion

            recRestructur.recRestructurSchedules = new List<recRestructurSchedule>();
            #region Adding recRestructurSchedule for each object recRestructur

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                  

                    string CommandSQl = "";
                    CommandSQl = @"SELECT  [RestructurScheduleID]
                                              ,[RestructurID]
                                              ,[ScheduleTime]
                                              ,[PaymentValue]
                                              ,[DueDate]
                                              ,[ScheduleNote]
                                              ,[CreatedDate]
                                              ,[CreatedBy]
                                              ,[UpdatedDate]
                                              ,[UpdatedBy]
                                          FROM [OJK Receivables].[transactions].[recRestructurSchedule]
                                          where [RestructurID] =" + recRestructur.RestructurID;


                    cmd.CommandText = CommandSQl.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {

                                recRestructurSchedule recschedul = new recRestructurSchedule();
                                recschedul.RestructurScheduleID = dr[recRestructurSchedule.Fields.RestructurScheduleID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurSchedule.Fields.RestructurScheduleID].ToString());
                                recschedul.RestructurID = dr[recRestructurSchedule.Fields.RestructurID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurSchedule.Fields.RestructurID].ToString());
                                recschedul.ScheduleTime = dr[recRestructurSchedule.Fields.ScheduleTime] == DBNull.Value ? string.Empty : dr[recRestructurSchedule.Fields.ScheduleTime].ToString();
                                recschedul.PaymentValue = dr[recRestructurSchedule.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recRestructurSchedule.Fields.PaymentValue].ToString());
                                recschedul.DueDate = dr[recRestructurSchedule.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurSchedule.Fields.DueDate].ToString());
                                recschedul.ScheduleNote = dr[recRestructurSchedule.Fields.ScheduleNote] == DBNull.Value ? string.Empty : dr[recRestructurSchedule.Fields.ScheduleNote].ToString();

                                recschedul.CreatedDate = dr[recRestructurSchedule.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurSchedule.Fields.CreatedDate].ToString());
                                recschedul.CreatedBy = dr[recRestructurSchedule.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recRestructurSchedule.Fields.CreatedBy].ToString();
                                recschedul.UpdatedDate = dr[recRestructurSchedule.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurSchedule.Fields.UpdatedDate].ToString());
                                recschedul.UpdatedBy = dr[recRestructurSchedule.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recRestructurSchedule.Fields.UpdatedBy].ToString();

                                recRestructur.recRestructurSchedules.Add(recschedul);
                                recschedul.Dispose();

                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }

            #endregion

            return recRestructur;
        }

        public List<recRestructur> GetListFullObject(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recRestructur> recRestructurs = new List<recRestructur>();

            recRestructurs = this.GetList(pageSize, currentPage, sortName, sortOrder, parameters);
            
            foreach (recRestructur rec in recRestructurs)
            {
                rec.recRestructurDetails = new List<recRestructurDetail>();
                #region Adding recRestructurDetail for each object recRestructur
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        int startRow = ((currentPage - 1) * pageSize) + 1;
                        int endRow = (startRow + pageSize);

                        string CommandSQl = "";
                        CommandSQl = @"SELECT  [RestructurDetailID]
                                              ,[RestructurID]
                                              ,[TransNo]
                                              ,[TransDemandNo]
                                              ,[SanctionNo]
                                              ,[TransTypeID]
                                              ,[TransTypeName]
                                              ,[TransValues]
                                              ,[CreatedDate]
                                              ,[CreatedBy]
                                              ,[UpdatedDate]
                                              ,[UpdatedBy]
                                          FROM [OJK Receivables].[transactions].[recRestructurDetail]
                                          where [RestructurID] = " + rec.RestructurID;


                        cmd.CommandText = CommandSQl.ToString();
                        cmd.CommandType = CommandType.Text;
                        try
                        {
                            conn.Open();
                            using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                            {
                                while (dr.Read())
                                {

                                    recRestructurDetail recdetal = new recRestructurDetail();
                                    recdetal.RestructurDetailID = dr[recRestructurDetail.Fields.RestructurDetailID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurDetail.Fields.RestructurDetailID].ToString());
                                    recdetal.RestructurID = dr[recRestructurDetail.Fields.RestructurID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurDetail.Fields.RestructurID].ToString());
                                    recdetal.TransNo = dr[recRestructurDetail.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransNo].ToString();
                                    recdetal.TransDemandNo = dr[recRestructurDetail.Fields.TransDemandNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransDemandNo].ToString();
                                    recdetal.SanctionNo = dr[recRestructurDetail.Fields.SanctionNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.SanctionNo].ToString();
                                    recdetal.TransTypeID = dr[recRestructurDetail.Fields.TransTypeID] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransTypeID].ToString();
                                    recdetal.TransTypeName = dr[recRestructurDetail.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransTypeName].ToString();

                                    recdetal.TransValues = dr[recRestructurDetail.Fields.TransValues] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recRestructurDetail.Fields.TransValues].ToString());

                                    recdetal.CreatedDate = dr[recRestructurDetail.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurDetail.Fields.CreatedDate].ToString());
                                    recdetal.CreatedBy = dr[recRestructurDetail.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.CreatedBy].ToString();
                                    recdetal.UpdatedDate = dr[recRestructurDetail.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurDetail.Fields.UpdatedDate].ToString());
                                    recdetal.UpdatedBy = dr[recRestructurDetail.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.UpdatedBy].ToString();

                                    rec.recRestructurDetails.Add(recdetal);
                                    recdetal.Dispose();

                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            throw (ex);
                        }
                        finally
                        {
                            if (conn.State == ConnectionState.Open) conn.Close();
                        }
                    }
                }

                #endregion

                rec.recRestructurSchedules = new List<recRestructurSchedule>();
                #region Adding recRestructurSchedule for each object recRestructur

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        int startRow = ((currentPage - 1) * pageSize) + 1;
                        int endRow = (startRow + pageSize);

                        string CommandSQl = "";
                        CommandSQl = @"SELECT  [RestructurScheduleID]
                                              ,[RestructurID]
                                              ,[ScheduleTime]
                                              ,[PaymentValue]
                                              ,[DueDate]
                                              ,[ScheduleNote]
                                              ,[CreatedDate]
                                              ,[CreatedBy]
                                              ,[UpdatedDate]
                                              ,[UpdatedBy]
                                          FROM [OJK Receivables].[transactions].[recRestructurSchedule]
                                          where [RestructurID] =" + rec.RestructurID;


                        cmd.CommandText = CommandSQl.ToString();
                        cmd.CommandType = CommandType.Text;
                        try
                        {
                            conn.Open();
                            using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                            {
                                while (dr.Read())
                                {

                                    recRestructurSchedule recschedul = new recRestructurSchedule();
                                    recschedul.RestructurScheduleID = dr[recRestructurSchedule.Fields.RestructurScheduleID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurSchedule.Fields.RestructurScheduleID].ToString());
                                    recschedul.RestructurID = dr[recRestructurSchedule.Fields.RestructurID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurSchedule.Fields.RestructurID].ToString());
                                    recschedul.ScheduleTime = dr[recRestructurSchedule.Fields.ScheduleTime] == DBNull.Value ? string.Empty : dr[recRestructurSchedule.Fields.ScheduleTime].ToString();
                                    recschedul.PaymentValue = dr[recRestructurSchedule.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recRestructurSchedule.Fields.PaymentValue].ToString());
                                    recschedul.DueDate = dr[recRestructurSchedule.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurSchedule.Fields.DueDate].ToString());
                                    recschedul.ScheduleNote = dr[recRestructurSchedule.Fields.ScheduleNote] == DBNull.Value ? string.Empty : dr[recRestructurSchedule.Fields.ScheduleNote].ToString();
                                    
                                    recschedul.CreatedDate = dr[recRestructurSchedule.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurSchedule.Fields.CreatedDate].ToString());
                                    recschedul.CreatedBy = dr[recRestructurSchedule.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recRestructurSchedule.Fields.CreatedBy].ToString();
                                    recschedul.UpdatedDate = dr[recRestructurSchedule.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurSchedule.Fields.UpdatedDate].ToString());
                                    recschedul.UpdatedBy = dr[recRestructurSchedule.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recRestructurSchedule.Fields.UpdatedBy].ToString();

                                    rec.recRestructurSchedules.Add(recschedul);
                                    recschedul.Dispose();

                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            throw (ex);
                        }
                        finally
                        {
                            if (conn.State == ConnectionState.Open) conn.Close();
                        }
                    }
                }

                #endregion
            }

            return recRestructurs;
        
        }

        public List<recDebtor> GetListDebitor()
        {
            List<recDebtor> recDebtors = new List<recDebtor>();
 
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    cmd.CommandText = @"SELECT [DebtorID]
                                      ,[DebtorCode]
                                      ,[DebtorName]
                                      ,[NPWP]
                                      ,[Address1]
                                      ,[Address2]
                                      ,[Address3]
                                      ,[ZipCode]
                                      ,[RegionID]
                                      ,[CityID]
                                      ,[Email]
                                      ,[Phone]
                                      ,[Fax]
                                      ,[Website]
                                      ,[IsActive]
                                      ,[CreatedDate]
                                      ,[CreatedBy]
                                      ,[UpdatedDate]
                                      ,[UpdatedBy]
                                      ,[IsIndividual]
                                  FROM [OJK Receivables].[master].[recDebtor]";
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtor t = new recDebtor();
                                t.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtor.Fields.DebtorID].ToString());
                                t.DebtorCode = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString();
                                t.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.NPWP = dr[recDebtor.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.NPWP].ToString();
                                t.Address1 = dr[recDebtor.Fields.Address1] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address1].ToString();
                                t.Address2 = dr[recDebtor.Fields.Address2] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address2].ToString();
                                t.Address3 = dr[recDebtor.Fields.Address3] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Address3].ToString();
                                t.ZipCode = dr[recDebtor.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.ZipCode].ToString();
                                t.RegionID = dr[recDebtor.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.RegionID].ToString());
                                t.CityID = dr[recDebtor.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recDebtor.Fields.CityID].ToString());
                                t.Email = dr[recDebtor.Fields.Email] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Email].ToString();
                                t.Phone = dr[recDebtor.Fields.Phone] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Phone].ToString();
                                t.Fax = dr[recDebtor.Fields.Fax] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Fax].ToString();
                                t.Website = dr[recDebtor.Fields.Website] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.Website].ToString();
                                t.IsActive = dr[recDebtor.Fields.IsActive] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsActive].ToString());
                                t.CreatedDate = dr[recDebtor.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recDebtor.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recDebtor.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recDebtor.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recDebtor.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.UpdatedBy].ToString();
                                t.IsIndividual = dr[recDebtor.Fields.IsIndividual] == DBNull.Value ? false : bool.Parse(dr[recDebtor.Fields.IsIndividual].ToString());
                                recDebtors.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
             }
            return recDebtors;
         }

        public List<recActionRestructur> GetListActionRestructure()
        {
            List<recActionRestructur> recActionRestructurs = new List<recActionRestructur>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT ActionRestructurID, ActionRestructurName, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM master.recActionRestructur";
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recActionRestructur recActionRestructur = new recActionRestructur();
                                recActionRestructur.ActionRestructurID = dr[recActionRestructur.Fields.ActionRestructurID] == DBNull.Value ? 0 : int.Parse(dr[recActionRestructur.Fields.ActionRestructurID].ToString());
                                recActionRestructur.ActionRestructurName = dr[recActionRestructur.Fields.ActionRestructurName] == DBNull.Value ? string.Empty : dr[recActionRestructur.Fields.ActionRestructurName].ToString();
                                recActionRestructur.CreatedDate = dr[recActionRestructur.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recActionRestructur.Fields.CreatedDate].ToString());
                                recActionRestructur.CreatedBy = dr[recActionRestructur.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recActionRestructur.Fields.CreatedBy].ToString();
                                recActionRestructur.UpdatedDate = dr[recActionRestructur.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recActionRestructur.Fields.UpdatedDate].ToString());
                                recActionRestructur.UpdatedBy = dr[recActionRestructur.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recActionRestructur.Fields.UpdatedBy].ToString();
                                recActionRestructurs.Add(recActionRestructur);
                                recActionRestructur.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return recActionRestructurs;
        }

        public int SaveReturnedID(recRestructur domain)
        {
            object result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO transactions.recRestructur(DebtorID,InvoiceNo , DebtorName, LetterNo, LetterDate, ActionRestructurID, ActionRestructurName, ScheduleTypeName, SchedulePayment, SumValues, RestructurValues, SumPayment, Balance, IsActive, AttachFile, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, AttachFileName) VALUES(@DebtorID, @InvoiceNo,@DebtorName, @LetterNo, @LetterDate, @ActionRestructurID, @ActionRestructurName, @ScheduleTypeName, @SchedulePayment, @SumValues, @RestructurValues, @SumPayment, @Balance, @IsActive, @AttachFile, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy,@AttachFileName)";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = domain.DebtorID;
                    cmd.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = domain.DebtorName;
                    cmd.Parameters.Add("@LetterNo", SqlDbType.VarChar).Value = (object)domain.LetterNo ?? DBNull.Value;
                    cmd.Parameters.Add("@LetterDate", SqlDbType.DateTime).Value = (object)domain.LetterDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ActionRestructurID", SqlDbType.VarChar).Value = (object)domain.ActionRestructurID ?? DBNull.Value;
                    cmd.Parameters.Add("@ActionRestructurName", SqlDbType.VarChar).Value = (object)domain.ActionRestructurName ?? DBNull.Value;
                    cmd.Parameters.Add("@ScheduleTypeName", SqlDbType.VarChar).Value = (object)domain.ScheduleTypeName ?? DBNull.Value;
                    cmd.Parameters.Add("@SchedulePayment", SqlDbType.Int).Value = (object)domain.SchedulePayment ?? DBNull.Value;
                    cmd.Parameters.Add("@SumValues", SqlDbType.Decimal).Value = (object)domain.SumValues ?? DBNull.Value;
                    cmd.Parameters.Add("@RestructurValues", SqlDbType.Decimal).Value = (object)domain.RestructurValues ?? DBNull.Value;
                    cmd.Parameters.Add("@SumPayment", SqlDbType.Decimal).Value = (object)domain.SumPayment ?? DBNull.Value;
                    cmd.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)domain.Balance ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@AttachFile", SqlDbType.VarBinary).Value = (object)domain.AttachFile ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@AttachFileName", SqlDbType.VarChar).Value = (object)domain.AttachFileName ?? DBNull.Value;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)domain.InvoiceNo ?? DBNull.Value;
                   
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "SELECT MAX(RestructurID) FROM transactions.recRestructur WHERE CreatedBy=@CreatedBy and CreatedDate=@CreatedDate";

                        result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return result == null ? -1 : int.Parse(result.ToString());
        }

        public string GetTransNo(string transTypeId) {
            object result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(@"DECLARE    @return_value int,
                                        @hasil varchar(100)
                                        EXEC    @return_value = [dbo].[SP_GenTransCode]
                                        @kode = N'{0}',
                                        @hasil = @hasil OUTPUT
                                        SELECT    @hasil as N'Hasil'", transTypeId);
                    cmd.CommandText = sbQuery.ToString();

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return result == null ? "" : result.ToString();
        }

        public bool UpdateWithoutFile(recRestructur domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE transactions.recRestructur SET DebtorID = @DebtorID,InvoiceNo = @InvoiceNo ,DebtorName = @DebtorName, LetterNo = @LetterNo, LetterDate = @LetterDate, ActionRestructurID = @ActionRestructurID, ActionRestructurName = @ActionRestructurName, ScheduleTypeName = @ScheduleTypeName, SchedulePayment = @SchedulePayment, SumValues = @SumValues, RestructurValues = @RestructurValues, SumPayment = @SumPayment, Balance = @Balance, IsActive = @IsActive, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE RestructurID = @RestructurID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@RestructurID", SqlDbType.Int).Value = domain.RestructurID;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = domain.DebtorID;
                    cmd.Parameters.Add("@DebtorName", SqlDbType.VarChar).Value = domain.DebtorName;
                    cmd.Parameters.Add("@LetterNo", SqlDbType.VarChar).Value = (object)domain.LetterNo ?? DBNull.Value;
                    cmd.Parameters.Add("@LetterDate", SqlDbType.DateTime).Value = (object)domain.LetterDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ActionRestructurID", SqlDbType.VarChar).Value = (object)domain.ActionRestructurID ?? DBNull.Value;
                    cmd.Parameters.Add("@ActionRestructurName", SqlDbType.VarChar).Value = (object)domain.ActionRestructurName ?? DBNull.Value;
                    cmd.Parameters.Add("@ScheduleTypeName", SqlDbType.VarChar).Value = (object)domain.ScheduleTypeName ?? DBNull.Value;
                    cmd.Parameters.Add("@SchedulePayment", SqlDbType.Int).Value = (object)domain.SchedulePayment ?? DBNull.Value;
                    cmd.Parameters.Add("@SumValues", SqlDbType.Decimal).Value = (object)domain.SumValues ?? DBNull.Value;
                    cmd.Parameters.Add("@RestructurValues", SqlDbType.Decimal).Value = (object)domain.RestructurValues ?? DBNull.Value;
                    cmd.Parameters.Add("@SumPayment", SqlDbType.Decimal).Value = (object)domain.SumPayment ?? DBNull.Value;
                    cmd.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)domain.Balance ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    //cmd.Parameters.Add("@AttachFile", SqlDbType.VarBinary).Value = (object)domain.AttachFile ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)domain.InvoiceNo ?? DBNull.Value;
                 
                   // cmd.Parameters.Add("@AttachFileName", SqlDbType.VarChar).Value = (object)domain.AttachFileName ?? DBNull.Value;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public List<recRestructurDetail> GetDetailListByResID(int ResID)
        {
            List<recRestructurDetail> tt = new List<recRestructurDetail>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT RestructurDetailID, RestructurID, TransNo, TransDemandNo, SanctionNo, TransTypeID, TransTypeName, TransValues, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM transactions.recRestructurDetail Where RestructurID="+ResID;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recRestructurDetail t = new recRestructurDetail();
                                t.RestructurDetailID = dr[recRestructurDetail.Fields.RestructurDetailID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurDetail.Fields.RestructurDetailID].ToString());
                                t.RestructurID = dr[recRestructurDetail.Fields.RestructurID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurDetail.Fields.RestructurID].ToString());
                                t.TransNo = dr[recRestructurDetail.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransNo].ToString();
                                t.TransDemandNo = dr[recRestructurDetail.Fields.TransDemandNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransDemandNo].ToString();
                                t.SanctionNo = dr[recRestructurDetail.Fields.SanctionNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.SanctionNo].ToString();
                                t.TransTypeID = dr[recRestructurDetail.Fields.TransTypeID] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransTypeID].ToString();
                                t.TransTypeName = dr[recRestructurDetail.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransTypeName].ToString();
                                t.TransValues = dr[recRestructurDetail.Fields.TransValues] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recRestructurDetail.Fields.TransValues].ToString());
                                t.CreatedDate = dr[recRestructurDetail.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurDetail.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recRestructurDetail.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recRestructurDetail.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurDetail.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recRestructurDetail.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recRestructurDetail> GetDetailListByDebtId(int DebtID)
        {
            List<recRestructurDetail> tt = new List<recRestructurDetail>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT a.RestructurDetailID, a.RestructurID, a.TransNo, a.TransDemandNo, a.SanctionNo, a.TransTypeID, a.TransTypeName, a.TransValues, a.CreatedDate, a.CreatedBy, a.UpdatedDate, a.UpdatedBy FROM transactions.recRestructurDetail a inner join transactions.recRestructur b on a.RestructurID = b.RestructurID Where b.DebtorID=" + DebtID;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recRestructurDetail t = new recRestructurDetail();
                                t.RestructurDetailID = dr[recRestructurDetail.Fields.RestructurDetailID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurDetail.Fields.RestructurDetailID].ToString());
                                t.RestructurID = dr[recRestructurDetail.Fields.RestructurID] == DBNull.Value ? 0 : int.Parse(dr[recRestructurDetail.Fields.RestructurID].ToString());
                                t.TransNo = dr[recRestructurDetail.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransNo].ToString();
                                t.TransDemandNo = dr[recRestructurDetail.Fields.TransDemandNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransDemandNo].ToString();
                                t.SanctionNo = dr[recRestructurDetail.Fields.SanctionNo] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.SanctionNo].ToString();
                                t.TransTypeID = dr[recRestructurDetail.Fields.TransTypeID] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransTypeID].ToString();
                                t.TransTypeName = dr[recRestructurDetail.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.TransTypeName].ToString();
                                t.TransValues = dr[recRestructurDetail.Fields.TransValues] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recRestructurDetail.Fields.TransValues].ToString());
                                t.CreatedDate = dr[recRestructurDetail.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurDetail.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recRestructurDetail.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recRestructurDetail.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recRestructurDetail.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recRestructurDetail.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recRestructurDetail.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public bool DeleteDetByRes(int key)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "DELETE FROM transactions.recRestructurDetail WHERE RestructurID = @RestructurID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@RestructurID", SqlDbType.Int).Value = key;

                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        result = true;
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }
        public bool DeleteSchedByRes(int key)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "DELETE FROM transactions.recRestructurSchedule WHERE RestructurID = @RestructurID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@RestructurID", SqlDbType.Int).Value = key;

                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        result = true;
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }

        public List<recDemand> GetListDebtOnDemand()
        {
            List<recDemand> tt = new List<recDemand>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT distinct a.DebtorID, a.DebtorName, b.DebtorCode FROM transactions.recDemand a inner join master.recDebtor b on a.debtorid=b.debtorid where a.isActive=0";
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDemand t = new recDemand();
                               t.DebtorID = dr[recDemand.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDemand.Fields.DebtorID].ToString());
                                t.DebtorName = dr[recDemand.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDemand.Fields.DebtorName].ToString();
                                t.CreatedBy = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString(); // Debtor Code
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public int UpdateIsRestructur(int ResID)
        {
            object result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"update transactions.recBilling set isrestructur = 1 where BillingNo in 
                    (select transdemandno from transactions.recrestructurdetail where restructurid = '" + ResID + "')";
                    cmd.CommandType = CommandType.Text;
                   
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        result = 1;
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return result == null ? -1 : int.Parse(result.ToString());
        }
        public int RollBackIsRestructur(int ResID)
        {
            object result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"update transactions.recBilling set isrestructur = 0 where billingno in 
                    (select transdemandno from transactions.recrestructurdetail where restructurid = '" + ResID + "')";
                    cmd.CommandType = CommandType.Text;

                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        result = 1;
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return result == null ? -1 : int.Parse(result.ToString());
        }
        public List<recDebtor> GetListDebtOnInvoice()
        {
            List<recDebtor> tt = new List<recDebtor>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT distinct a.DebtorID, a.DebtorName, a.DebtorCode FROM master.recDebtor a inner join master.recDebtorSector b on a.debtorid=b.debtorid inner join transactions.recInvoice c on b.debtorsectorid = c.debtorsectorid";
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recDebtor t = new recDebtor();
                                t.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtor.Fields.DebtorID].ToString());
                                t.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.DebtorCode = dr[recDebtor.Fields.DebtorCode] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorCode].ToString(); // Debtor Code
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
    }
}
